package weaver.aiyh_pcn.async_organization.util;

import aiyh.utils.zwl.common.ToolUtil;
import com.ibm.icu.text.MessageFormat;
import com.ibm.icu.text.SimpleDateFormat;
import com.weaver.formmodel.util.EncryptHelper;
import com.weaver.general.TimeUtil;
import com.weaver.general.Util;
import org.h2.util.StringUtils;
import weaver.aiyh_pcn.async_organization.model.Department;
import weaver.aiyh_pcn.async_organization.model.Employee;
import weaver.aiyh_pcn.async_organization.model.Position;
import weaver.conn.RecordSet;
import java.lang.reflect.InvocationTargetException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @author EBU7-dev1-ayh
 * @create 2021/7/22 0022 10:06
 * 同步人员组织结构工具类
 */


public class SyncOrganizationUtils extends ToolUtil {
	private static final String className = "SyncOrganization_Cronjob";

	public Map<String, String> asyncDepartment(Department department) {
//		writeErrorLog( "---------------"+className+" asyncDepartment  Begin --------------------");
		GetModelValue<Department> getModelValue = new GetModelValue<>();
		Map<String, String> successMark = new HashMap<>();
		RecordSet rs = new RecordSet();
//		所属分部id
		int subcompanyid1 = 0;
//		上级部门的id
		int supDepId = 0;
//		如果该部门是属于根部门，则需要进行分部的同步
		//		 查询手动维护的分部信息表，将分部信息保存到分部表hrmsubcompany中，并且将部门的分部id赋值
		if (department.getPARENTDEPARTMENDID() == 0) {
//			查询mapping表，查看分部与部门的映射关系,通过部门id查询分部信息
			String querySql = "select * from uf_sub_mapping where frist_company = "
					+ Util.null2String(String.valueOf(department.getDEPARTMENTID()));
//			查询数据库
			try {
				rs.executeQuery(querySql);
			} catch (Exception e) {
				writeErrorLog(className, "select subcomany error:" + querySql);
				writeErrorLog("error info:" + e.getMessage() + "\n");
				successMark.put("code", "0");
				successMark.put("msg", "查询分部信息出错，错误SQL：" + querySql);
			}
			if (rs.next()) {
				subcompanyid1 = Util.getIntValue(rs.getString("subcompany"), 0);
			}

//			Map<String, Integer> stringIntegerMap = this.subCompanyHandler(department);
//			subcompanyid1 = stringIntegerMap.get("subcompanyid1");
//			supDepId = stringIntegerMap.get("supDepId");
		} else {
//			如果不是根部门，需要查询到他的父级部门，然后查出他的分部id
//			查询部门的父级部门
			try {
				rs.executeQuery("select * from hrmdepartment where outkey = ?", Util.null2String(String.valueOf(department.getPARENTDEPARTMENDID())));
			} catch (Exception e) {
				writeErrorLog("select hrmdepartment error in 51 line,sql: select * from hrmdepartment where outkey = " + Util.null2String(String.valueOf(department.getPARENTDEPARTMENDID())));
				writeErrorLog("error info:" + e.getMessage() + "\n");
				successMark.put("code", "0");
				successMark.put("msg", "查询分部信息出错，错误SQL：" + "select hrmdepartment error in 51 line,sql: select * from hrmdepartment where outkey = " + Util.null2String(String.valueOf(department.getPARENTDEPARTMENDID())));
			}
			rs.next();
//			将上级部门的分部id保存起来
			subcompanyid1 = Util.getIntValue(Util.null2String(rs.getString("subcompanyid1")));
//			将上级部门的id保存起来
			supDepId = Util.getIntValue(Util.null2String(rs.getString("id")));
		}
//      处理部门信息
// 		查询HR同步配置表用于更新或者插入字段  查询配置表
		Map<String, Map<String, String>> configTableInfo = this.queryConfigTableInfo(department, getModelValue, "m.api_field_type = 0");
		Map<String, String> sysDep = configTableInfo.get("sys");
		Map<String, String> defDep = configTableInfo.get("def");

//          查询部门表，是否存在该部门
		String depSql = "select * from hrmdepartment where outkey = " + department.getDEPARTMENTID();
		try {
			rs.executeQuery(depSql);
		} catch (Exception e) {
			writeErrorLog("select hrmdepartment error in 70 line, sql: " + depSql);
			writeErrorLog("error info:" + e.getMessage() + "\n");
			successMark.put("code", "0");
			successMark.put("msg", "查询分部信息出错，错误SQL：" + depSql);
		}
		int finalSubcompanyid = subcompanyid1;
		if (rs.next()) {
//				存在该部门
//				拼接更新SQL
//			查询父级部门的id并更新
			String querySupId = "select * from hrmdepartment where outkey = ?";
			RecordSet r_rs = new RecordSet();
			r_rs.executeQuery(querySupId, department.getPARENTDEPARTMENDID());
			String supDepId1;
			if (r_rs.next()) {
				supDepId1 = r_rs.getString("id");
				sysDep.put("supdepid", supDepId1);
			}
			sysDep.put("subcompanyid1", String.valueOf(finalSubcompanyid));
			StringBuilder sysSetSql = this.builderSql(sysDep, 1).get("update");
			StringBuilder defSetSql = this.builderSql(defDep, 1).get("update");
//				添加更新默认值
			Map<String, Object> sysMap = new HashMap<>();
			sysMap.put("modified", this.getTime());
			this.addDefaultUpdateSql(sysSetSql, sysMap);
//				更新数据库
			this.executeUpdateData(sysSetSql, "hrmdepartment", "id = " + Util.null2String(rs.getString("id")));
			this.executeUpdateData(defSetSql, "hrmdepartmentdefined", "deptid = " + Util.null2String(rs.getString("id")));
			successMark.put("code", "2");
			successMark.put("msg", "更新部门" + department.getPARENTDEPARTMENDID() + "成功！");
		} else {
//				拼接插入SQL
//				不存在部门信息
			Map<String, StringBuilder> insertSysBuilderMap = this.builderSql(sysDep, 2);
			StringBuilder sysInsertSqlField = insertSysBuilderMap.get("insertField");
			StringBuilder sysInsertSqlValue = insertSysBuilderMap.get("insertValue");
			Map<String, StringBuilder> insertDefBuilderMap = this.builderSql(defDep, 2);
			StringBuilder defInsertSqlField = insertDefBuilderMap.get("insertField");
			StringBuilder defInsertSqlValue = insertDefBuilderMap.get("insertValue");
//				加入默认值
			int finalSupDepId = supDepId;

			Map<String, Object> map = new HashMap<String, Object>() {{
				//			外部系统主键
				put("outkey", Util.getIntValue(String.valueOf(department.getDEPARTMENTID()), 0));
				//			所属分部id
				put("subcompanyid1", finalSubcompanyid);
				//			上级部门id
				put("supdepid", finalSupDepId);
				put("departmentmark", Util.null2String(department.getDEPARTMENTNAME()));
				put("departmentname", Util.null2String(department.getDEPARTMENTNAME()));
			}};
			//			创建时间
			map.put("created", this.getTime());
			this.addDefaultInsertSql(sysInsertSqlField, sysInsertSqlValue, map);
			int departmentId = 0;
			this.executeInsertData(sysInsertSqlField, sysInsertSqlValue, "hrmdepartment");
//			查询刚添加的部门id
			try {
				rs.executeQuery("select * from hrmdepartment where outkey = ?", Util.null2String(String.valueOf(department.getDEPARTMENTID())));
			} catch (Exception e) {
				writeErrorLog("select hrmdepartment error in 112 line, sql: select * from hrmdepartment where outkey = " + Util.null2String(String.valueOf(department.getDEPARTMENTID())));
				writeErrorLog("error info:" + e.getMessage() + "\n");
				successMark.put("code", "0");
				successMark.put("msg", "查询分部信息出错，错误SQL：" + "select * from hrmdepartment where outkey = " + Util.null2String(String.valueOf(department.getDEPARTMENTID())));
			}
			rs.next();
			departmentId = Util.getIntValue(Util.null2String(rs.getString("id")));
//			设置默认值
			if (defInsertSqlField.lastIndexOf(",") != -1 && defInsertSqlValue.lastIndexOf(",") != -1) {
				this.addDefaultInsertSql(defInsertSqlField, defInsertSqlValue, "deptid", departmentId);
			}
			this.executeInsertData(defInsertSqlField, defInsertSqlValue, "hrmdepartmentdefined");
			successMark.put("code", "1");
			successMark.put("msg", "插入部门" + department.getPARENTDEPARTMENDID() + "成功！");
		}
		return successMark;
//		writeErrorLog( "---------------"+className+" asyncDepartment  end --------------------");
	}


	public Map<String, String> asyncPosition(Position position) {
//		writeErrorLog( "---------------"+className+" asyncPosition  Begin --------------------");
//		 职位同步的SQL
		RecordSet rs = new RecordSet();
		Map<String, String> successMark = new HashMap<>();
		try {
			rs.executeQuery("select id from hrmjobtitles where outkey = ?", Util.getIntValues(String.valueOf(position.getJOBCODEID())));
		} catch (Exception e) {
			writeErrorLog("select htmjobtitles error in 131 line, sql: select id from hrmjobtitles where outkey = " + Util.getIntValues(String.valueOf(position.getJOBCODEID())));
			writeErrorLog("error info:" + e.getMessage() + "\n");
			successMark.put("code", "0");
			successMark.put("msg", "查询职位错误，错误SQL：" + "select id from hrmjobtitles where outkey = " + Util.getIntValues(String.valueOf(position.getJOBCODEID())));
		}
//		判断该职位是否存在于系统中
		if (rs.next()) {
//			存在该条职位，需要进行职位的更新
			String updateSqlStr = MessageFormat.format("update hrmjobtitles set " +
							"modified = ''{0}'', jobtitlemark = ''{1}'', jobtitlename = ''{2}'', cmsSupJobId = ''{3}'', " +
							"jobtitlecode = ''{4}'' where outkey = ''{5}''",
					this.getTime(), Util.null2String(position.getJOBFUNCTION()), Util.null2String(position.getJOBFUNCTION()),
					Util.null2String(String.valueOf(position.getSUPERIORJOBCODEID())), Util.null2String(position.getJOBCODE()), Util.null2String(String.valueOf(position.getJOBCODEID())));
			//			id
			try {
				rs.executeUpdate(updateSqlStr);
				successMark.put("code", "2");
				successMark.put("msg", "更新职位" + position.getJOBCODEID() + "成功！");
			} catch (Exception e) {
				writeErrorLog(className, "update jobtitle error: " + updateSqlStr);
				writeErrorLog("error info:" + e.getMessage() + "\n");
				successMark.put("code", "0");
				successMark.put("msg", "更新职位失败，错误SQL：" + updateSqlStr);
			}
		} else {

//			不存在该条职位
//			判断该职位是否属于根职位（是否存在上级职位id），如果属于根职位，则需要进行创建工作类型（插入数据到工作类型表中）
//              并且创建职责数据，然后将数据插入到职位表中
//			如果不是根职位，则需要判断其上级职位是否属于根职位，如果上级职位属于根职位，则需要插入职责表，同时工作类型为父级岗位的
//			    工作类型，然后将数据插入到职位表中，职责类型为刚插入的职责数据id。如果上级职位不属于根职位，则插入数据到职位表中，
//			    职责id为父级职位的职责id

//			判断是否是根职位
			if (position.getSUPERIORJOBCODEID() == 0) {
//              是根职位,插入数据到工作类型表中hrmjobgroups
				int groupId = this.insertJobGroup(position);
//				插入数据到工作职责表中，并且绑定工作类型id为刚插入的数据的id
				int activityId = this.insertJobActive(position, groupId);
//				将职位插入到职位表中，职责id为刚插入数据的id
				this.insertJobTitle(position, rs, activityId, 0);
			} else {
//				不是根职位，判断父职位是否属于根职位
				try {
					rs.executeQuery("select * from hrmjobtitles where outkey = ?", Util.getIntValues(String.valueOf(position.getSUPERIORJOBCODEID())));
				} catch (Exception e) {
					writeErrorLog("select htmjobtitles error in 166 line, sql: select * from hrmjobtitles where outkey = " + Util.getIntValues(String.valueOf(position.getJOBCODEID())));
					writeErrorLog("error info:" + e.getMessage() + "\n");
					successMark.put("code", "0");
					successMark.put("msg", "查询父级职位失败，错误SQL：" + "select * from hrmjobtitles where outkey = " + Util.getIntValues(String.valueOf(position.getSUPERIORJOBCODEID())));
				}
				if (rs.next()) {
					if (Util.getIntValue(Util.null2String(rs.getString("cmssupjobid"))) == 0) {
//					    父职位属于根职位，获取父级职位的工作类型id
						try {
							rs.executeQuery("select * from hrmjobactivities where id = ?", Util.getIntValue(Util.null2String(rs.getString("jobactivityid"))));
						} catch (Exception e) {
							writeErrorLog("select hrmjobactivities error in 175 line, sql: select * from hrmjobactivities where id = " + Util.getIntValue(Util.null2String(rs.getString("jobactivityid"))));
							writeErrorLog("error info:" + e.getMessage() + "\n");
							successMark.put("code", "0");
							successMark.put("msg", "查询父职位工作类型错误，错误SQL：" + "select * from hrmjobactivities where id = " + Util.getIntValue(Util.null2String(rs.getString("jobactivityid"))));
						}
						rs.next();
						int groupId = Util.getIntValue(Util.null2String(rs.getString("jobgroupid")));
//					    将数据插入到工作职责表中
						int activityId = this.insertJobActive(position, groupId);
//						将数据插入到职位表中
						this.insertJobTitle(position, rs, activityId, Util.getIntValue(String.valueOf(position.getSUPERIORJOBCODEID())));
					} else {
//						父级职位不属于根职位，获取父级职责id并将数据插入到职位表中
						int activityId = Util.getIntValue(Util.null2String(rs.getString("jobactivityid")));
//						将数据插入到职位表中
						this.insertJobTitle(position, rs, activityId, Util.getIntValue(String.valueOf(position.getSUPERIORJOBCODEID())));
					}
				}
			}
			successMark.put("code", "1");
			successMark.put("msg", "插入" + position.getJOBCODEID() + "成功！");
		}
		return successMark;
//		writeErrorLog( "---------------"+className+" asyncPosition  end --------------------");
	}


	public Map<String, String> asyncEmployee(Employee employee) {
//		writeErrorLog( "---------------"+className+" asyncEmployee  Begin --------------------");
//      直接上级
		int managerId = 0;
		String managerStr = "";
		Map<String, String> successMark = new HashMap<>();
//      安全级别
		String seclevel = getSystemParamValue("HR_SECLEVEL");
		if (seclevel == null || "".equals(seclevel)) {
			seclevel = "0";
		}
		String password = getSystemParamValue("PASSWORD");
		if (StringUtils.isNullOrEmpty(password)) {
			password = employee.getUSERCODE();
		}
//		人员id
		int hrmid = 0;
//		 不知道是否需要判断组织编码是否存在
		GetModelValue<Employee> getModelValue = new GetModelValue<>();
		RecordSet rs = new RecordSet();
//		查询配置表信息
		Map<String, Map<String, String>> configTableInfo = this.queryConfigTableInfo(employee, getModelValue, "m.api_field_type = 1");
		Map<String, String> sysDep = configTableInfo.get("sys");
		Map<String, String> defDep = configTableInfo.get("def");
//		获取自定义字段的scopeid
		rs.executeQuery("select * from cus_formfield");

//		查询人员的职责表，获取分部id，和直接上级id
		Map<String, Object> subIdAndManagerIdMap = this.getSubIdAndManagerId(employee);
		managerId = Util.getIntValue(String.valueOf(subIdAndManagerIdMap.get("managerId")), 0);
		String departmentid = subIdAndManagerIdMap.get("departmentId").toString();
		String subcompanyid1 = subIdAndManagerIdMap.get("subCompanyId").toString();
		String today = TimeUtil.getCurrentDateString();
		//	    根据outkey,询是否存在该人员
		String query = "select id from hrmresource where outkey = ?";
		try {
			rs.executeQuery(query, Util.null2String(String.valueOf(employee.getUserID())));
		} catch (Exception e) {
			writeErrorLog("select hrmresource error: " + query + Util.null2String(String.valueOf(employee.getUserID())));
			writeErrorLog("error info:" + e.getMessage() + "\n");
			successMark.put("code", "0");
			successMark.put("msg", "查询出错，错误SQL：" + "select hrmresource error: " + query + Util.null2String(String.valueOf(employee.getUserID())));
		}
		if (rs.next()) {
//			存在人员信息，获取到人员id
			hrmid = Util.getIntValue(rs.getString("id"));
//			更新人员信息,拼接更新SQL
			StringBuilder sysSetSql = this.builderSql(sysDep, 1).get("update");
//			StringBuilder defSetSql = this.builderSql(defDep, employee, getModelValue,1).get("update");
			Map<String, Object> sysMap = new HashMap<String, Object>() {{
				put("departmentid", subIdAndManagerIdMap.get("departmentId"));
				put("subcompanyid1", subIdAndManagerIdMap.get("subCompanyId"));
			}};
			//			查询该人员的职位信息
//				查询到当前职位的id
			String jobId = String.valueOf(subIdAndManagerIdMap.get("jobtitleId"));
			sysMap.put("jobtitle", jobId);
			if (null != subIdAndManagerIdMap.get("managerstr")) {
				managerStr = subIdAndManagerIdMap.get("managerstr").toString();
				if (!managerStr.startsWith(",")) {
					managerStr = "," + managerStr;
				}
				if (!managerStr.endsWith(",")) {
					managerStr = managerStr + ",";
				}
				managerStr = managerStr + managerId + ",";
				sysMap.put("managerstr", managerStr);
				sysMap.put("managerid", managerId);
			}

			sysMap.put("managerid", managerId);
			sysMap.put("modified", this.getTime());

			this.addDefaultUpdateSql(sysSetSql, sysMap);

			this.executeUpdateData(sysSetSql, "hrmresource", "id = '" + hrmid + "'");
//			 待完善更新自定义人员信息表
//			判断该字段的scopeid
			for (Map.Entry<String, String> entry : defDep.entrySet()) {
//				判断该字段属于的scopeid
				String fieldId = entry.getKey().replace("field", "");
				try {
					rs.executeQuery("select * from cus_formfield where fieldid = '" + Util.null2String(fieldId) + "' and scope = 'HrmCustomFieldByInfoType'");
				} catch (Exception e) {
					writeErrorLog("select cus_fromfield error: select * from cus_formfield where fieldid = '" + Util.null2String(fieldId) + "' and scope = 'HrmCustomFieldByInfoType'");
					writeErrorLog("error info:" + e.getMessage() + "\n");
				}
				rs.next();
				String scopeid = Util.null2String(rs.getString("scopeid"));
//				 判断是否存在该信息，如果存在着更新，不存在着插入
				rs.executeQuery("select id from cus_fielddata where id = '" + hrmid + "' and scope = 'HrmCustomFieldByInfoType' and scopeid = '" + scopeid + "'");
				if (rs.next()) {
//					存在该信息,更新指定信息
					try {
						rs.executeUpdate("update cus_fielddata set " + entry.getKey() + " = '" + entry.getValue() + "' where scopeid = " + scopeid + " and id = " + hrmid);
					} catch (Exception e) {
						writeErrorLog(className, "update error, sql:  update cus_fielddata set " + entry.getKey() + " = " + entry.getValue() + " where scopeid = \" + scopeid +\" and id = \" + hrmid");
						writeErrorLog("error info:" + e.getMessage() + "\n");
					}

				} else {
//					不存在该信息，插入
					try {
						rs.executeUpdate("insert into cus_fielddata (id,scopeid,scope," + entry.getKey() + ") values ('" + hrmid + "','" + scopeid + "','HrmCustomFieldByInfoType','" + entry.getValue() + "')");
					} catch (Exception e) {
						writeErrorLog(className, "insert into error, sql : \"insert into cus_fielddata (id,scopeid,scope,\" + entry.getKey() + \") values ('\" + hrmid + \"','\" + scopeid + \"','HrmCustomFieldByInfoType','\" + entry.getValue() + \"')\"");
						writeErrorLog("error info:" + e.getMessage() + "\n");
					}
				}
//
			}
//			this.executeUpdateData(defSetSql,"cus_fielddata","");
			successMark.put("code", "2");
			successMark.put("msg", "更新成功！");
		} else {
//			不存在人员信息需要将人员信息插入到数据库中
//			获取当前人员表中的最大id
			rs.executeProc("HrmResourceMaxId_Get", "");
			rs.next();
			hrmid = rs.getInt(1);
			if (managerId == 0) {
				managerId = hrmid;
			}
//			拼接插入SQL
			Map<String, StringBuilder> insertSysBuilderMap = this.builderSql(sysDep, 2);
			StringBuilder sysInsertSqlField = insertSysBuilderMap.get("insertField");
			StringBuilder sysInsertSqlValue = insertSysBuilderMap.get("insertValue");
//				加入默认值
			String finalSeclevel = seclevel;
			Map<String, Object> map = new HashMap<String, Object>() {{
				//	外部系统主键
				put("outkey", Util.null2String(String.valueOf(employee.getUserID())));
				//	所属分部id
				put("subcompanyid1", subIdAndManagerIdMap.get("subCompanyId"));
				//	部门id
				put("departmentid", subIdAndManagerIdMap.get("departmentId"));
//				安全级别
				put("seclevel", finalSeclevel);
//				员工编号
				put("workcode", Util.null2String(employee.getUSERCODE()));
//				姓名
				put("lastname", Util.null2String(employee.getPreferred_Name()) + "/" + Util.null2String(employee.getUSERNAMECN()));

//				岗位id
				put("jobtitle", subIdAndManagerIdMap.get("jobtitleId"));
//				状态
				put("status", 1);
//				电话
				put("telephone", Util.null2String(employee.getTEL()));
//				移动电话
				put("mobile", Util.null2String(employee.getMOBILENO()));
//				电子邮件
				put("email", Util.null2String(employee.getEMAIL()));

			}};
			if (null != subIdAndManagerIdMap.get("managerstr")) {
				managerStr = subIdAndManagerIdMap.get("managerstr").toString();
				if (!managerStr.startsWith(",")) {
					managerStr = "," + managerStr;
				}
				if (!managerStr.endsWith(",")) {
					managerStr = managerStr + ",";
				}
				managerStr = managerStr + managerId + ",";
				map.put("managerstr", managerStr);
			}
//			userCode不为null的人员
			if (!StringUtils.isNullOrEmpty(employee.getUSERCODE())) {
//				登录名
				map.put("loginid", employee.getUSERCODE());
//			密码
				map.put("password", EncryptHelper.encodeMd5(password).toUpperCase());
			} else {
				writeErrorLog("the employee hove not usercode: " + employee);
			}

//			id
			map.put("id", hrmid);
//			直接上级
			map.put("managerid", managerId);
			//			创建时间
			map.put("created", this.getTime());
//			拼接默认值SQL
			this.addDefaultInsertSql(sysInsertSqlField, sysInsertSqlValue, map);
			//			插入数据
			this.executeInsertData(sysInsertSqlField, sysInsertSqlValue, "hrmresource");
			try {
				if (departmentid == null || "".equals(departmentid)) {
					departmentid = "0";
				}
				if (subcompanyid1 == null || "".equals(subcompanyid1)) {
					subcompanyid1 = "0";
				}
				//共享信息
				char separator = Util.getSeparator();
				String p_para = "" + hrmid + separator + departmentid + separator + subcompanyid1 + separator + managerId + separator + seclevel + separator + managerStr +
						separator + "0" + separator + "0" + separator + "0" + separator + "0" + separator + "0" + separator + "0";
//				this.writeDebuggerLog(p_para);
				rs.executeProc("HrmResourceShare", p_para);
				rs.executeProc("HrmResource_CreateInfo", "" + hrmid + separator + "1" + separator + today + separator + "1" + separator + today);
				//触发器
				String para = "" + hrmid + separator + managerId + separator + departmentid + separator + subcompanyid1 + separator + "0" + separator + managerStr;
				rs.executeProc("HrmResource_Trigger_Insert", para);
				//入职维护状态
				rs.executeUpdate("insert into HrmInfoStatus (itemid,hrmid,status) values(1," + hrmid + ",1)");

				rs.executeUpdate("insert into HrmInfoStatus (itemid,hrmid) values(2," + hrmid + ")");

				rs.executeUpdate("insert into HrmInfoStatus (itemid,hrmid) values(3," + hrmid + ")");

				rs.executeUpdate("insert into HrmInfoStatus (itemid,hrmid) values(10," + hrmid + ")");
			} catch (Exception e) {
				writeErrorLog("error info:" + e.getMessage() + "\n");
			}
//			插入自定义信息表
			for (Map.Entry<String, String> entry : defDep.entrySet()) {
//				判断该字段属于的scopeid
				String fieldId = entry.getKey().replace("field", "");
				try {
					rs.executeQuery("select * from cus_formfield where fieldid = '" + Util.null2String(fieldId) + "' and scope = 'HrmCustomFieldByInfoType'");
				} catch (Exception e) {
					writeErrorLog("select cus_fromfield error: select * from cus_formfield where fieldid = '" + Util.null2String(fieldId) + "' and scope = 'HrmCustomFieldByInfoType'");
					writeErrorLog("error info:" + e.getMessage() + "\n");
				}
				rs.next();
				String scopeid = rs.getString("scopeid");
//				插入数据表中指定字段信息
				rs.executeUpdate("insert into cus_fielddata (id,scopeid,scope," + entry.getKey() + ") values ('" + hrmid + "','" + scopeid + "','HrmCustomFieldByInfoType','" + entry.getValue() + "')");
			}
			successMark.put("code", "1");
			successMark.put("msg", "插入成功！");
		}
		return successMark;
//		writeErrorLog( "---------------"+className+" asyncEmployee  end --------------------");
	}

	private void addDefaultUpdateSql(StringBuilder sqlBuilder, Map<String, Object> map) {
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			sqlBuilder.append(entry.getKey());
			sqlBuilder.append(" = ");
			sqlBuilder.append("'");
			sqlBuilder.append(entry.getValue());
			sqlBuilder.append("',");
		}
	}

	/**
	 * 获取直接上级
	 * @param employee 人员对象
	 * @return 获取人员直接上级，分部id和部门id
	 */
	private Map<String, Object> getSubIdAndManagerId(Employee employee) {
		Map<String, Object> map = new HashMap<>();
		RecordSet rs = new RecordSet();
//		通过部门id查询该员工的分部id
		String querySubIdAndId = "select id, subcompanyid1 from hrmdepartment where outkey = ?";
		try {
			rs.executeQuery(querySubIdAndId, Util.null2String(String.valueOf(employee.getDEPARTMENTID())));
		} catch (Exception e) {
			writeErrorLog("select hrmdepartment error: " + querySubIdAndId + Util.null2String(String.valueOf(employee.getDEPARTMENTID())));
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}
		rs.next();
//		该员工所属部门id
		String departmentId = Util.null2String(rs.getString("id"));
//		该员工所属分部id
		String subCompanyId = Util.null2String(rs.getString("subcompanyid1"));
		map.put("subCompanyId", subCompanyId);
		map.put("departmentId", departmentId);
//		查询该员工的直接上级id => 查询该员工的职位的cmssupjobid对应的职位
//		由于需要判断该员工的职位是否存在上级职位，所以不能一条SQL搞定，首先需要查询该员工的职位的上级职位id，也就是判断是否存在上级职位
		String queryJobTitleId = "select id,cmssupjobid from hrmjobtitles where outkey = ?";
//		 不知道需不需要在维护一下职位表中的所属部门id，如果需要的haunt将部门id更新进去就可以
//		查询当前人员对应的职位id以及cms中的上级职位id
		try {
			rs.executeQuery(queryJobTitleId, Util.null2String(String.valueOf(employee.getJOBCODEID())));
		} catch (Exception e) {
			writeErrorLog("select htmjobtitles error: " + queryJobTitleId + Util.null2String(String.valueOf(employee.getJOBCODEID())));
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}
		rs.next();
//	    获取职位id
		map.put("jobtitleId", Util.null2String(rs.getString("id")));
//		获取cms中的上级职位id，如果存在则查询上级职位的id，如果不存在则表示该人员表示没有直接上级
		int cmsSupId = Util.getIntValue(Util.null2String(rs.getString("cmssupjobid")), 0);
/*		if (cmsSupId != 0) {
//			表示该职位拥有上级职位，需要查询上级职位对应的人员
			String queryManagerId = "select h.id,h.managerstr from hrmresource as h join hrmjobtitles as j on h.jobtitle = j.id where j.outkey = ?";
			try {
				rs.executeQuery(queryManagerId, Util.null2String(String.valueOf(cmsSupId)));
			} catch (Exception e) {
				writeErrorLog("select hrmresource error: " + queryManagerId + Util.null2String(String.valueOf(cmsSupId)));
				writeErrorLog("error info:" + e.getMessage() + "\n");
			}
			if (rs.next()) {
				map.put("managerId", Util.getIntValue(Util.null2String(rs.getString("id"))));
				map.put("managerstr", Util.null2String(rs.getString("managerstr")));
			} else {
				map.put("managerId", 0);
			}
		} else {
//			如果该职位没有上级职位，则将该员工的直接上级id设置为0
			map.put("managerId", 0);
		}*/
		while (cmsSupId >= 0) {
//			表示该职位拥有上级职位，需要查询上级职位对应的人员
			String queryManagerId = "select h.id,h.managerstr from hrmresource as h join hrmjobtitles as j on h.jobtitle = j.id where j.outkey = ?";
			try {
				rs.executeQuery(queryManagerId, Util.null2String(String.valueOf(cmsSupId)));
			} catch (Exception e) {
				writeErrorLog("select hrmresource error: " + queryManagerId + Util.null2String(String.valueOf(cmsSupId)));
				writeErrorLog("error info:" + e.getMessage() + "\n");
			}
			if (rs.next()) {
				map.put("managerId", Util.getIntValue(Util.null2String(rs.getString("id"))));
				map.put("managerstr", Util.null2String(rs.getString("managerstr")));
				break;
			} else {
				if (cmsSupId == 0) {
					break;
				}
				queryJobTitleId = "select id,cmssupjobid from hrmjobtitles where outkey = ?";
//		查询当前人员对应的职位id以及cms中的上级职位id
				try {
					rs.executeQuery(queryJobTitleId, Util.null2String(String.valueOf(cmsSupId)));
				} catch (Exception e) {
					writeErrorLog("select htmjobtitles error: " + queryJobTitleId + Util.null2String(String.valueOf(employee.getJOBCODEID())));
					writeErrorLog("error info:" + e.getMessage() + "\n");
				}
				rs.next();
				cmsSupId = Util.getIntValue(Util.null2String(rs.getString("cmssupjobid")));
				map.put("managerId", 0);
			}
		}
		/*else {
//			如果该职位没有上级职位，则将该员工的直接上级id设置为0
			map.put("managerId", 0);
		}*/
		return map;
	}

	/**
	 * 执行插入SQL
	 * @param insertFieldBuilder 需要插入的的字段
	 * @param insertValueBuilder 需要插入的字段对应的值
	 * @param table 表名
	 */
	private void executeInsertData(StringBuilder insertFieldBuilder, StringBuilder insertValueBuilder, String table) {
		RecordSet rs = new RecordSet();
		if (insertFieldBuilder.lastIndexOf(",") != -1 && insertValueBuilder.lastIndexOf(",") != -1) {
//				    去除最后一个逗号
			String insertFiledStr = this.removeLastComma(insertFieldBuilder);
			String insertValueStr = this.removeLastComma(insertValueBuilder);
			String insertSysSql = "insert into " + table + " (" + insertFiledStr + ") values (" + insertValueStr + ")";
			try {
				rs.executeUpdate(insertSysSql);
			} catch (Exception e) {
				writeErrorLog("insert " + table + " error: " + insertSysSql);
				writeErrorLog("error info:" + e.getMessage() + "\n");
			}

		}
	}

	/**
	 * 执行更新语句
	 * @param updateSqlBuilder 更新SQL的字符串
	 * @param table 表名
	 * @param conditions 条件
	 */
	private void executeUpdateData(StringBuilder updateSqlBuilder, String table, String conditions) {
		RecordSet rs = new RecordSet();
		if (updateSqlBuilder.lastIndexOf(",") != -1) {
			String updateSqlBuilderStr = updateSqlBuilder.substring(0, updateSqlBuilder.lastIndexOf(","));
			String updateSql = "update " + table + " set " + updateSqlBuilderStr + " where " + conditions;
			try {
				rs.executeUpdate(updateSql);
			} catch (Exception e) {
				writeErrorLog("update " + table + " error: " + updateSql);
				writeErrorLog("error info:" + e.getMessage() + "\n");
			}
		}
	}

	private String removeLastComma(StringBuilder sqlBuilder) {
		return sqlBuilder.substring(0, sqlBuilder.lastIndexOf(","));
	}

	private void addDefaultInsertSql(StringBuilder filedBuilder, StringBuilder valueBuilder, String filedName, Object value) {
		filedBuilder.append(filedName);
		filedBuilder.append(",");
		valueBuilder.append("'");
		valueBuilder.append(value);
		valueBuilder.append("',");
	}

	private void addDefaultInsertSql(StringBuilder filedBuilder, StringBuilder valueBuilder, Map<String, Object> map) {
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			filedBuilder.append(entry.getKey());
			filedBuilder.append(",");
			valueBuilder.append("'");
			valueBuilder.append(entry.getValue());
			valueBuilder.append("',");
		}
	}

	/**
	 * 分部信息处理
	 * @param department 部门
	 * @return
	 */
	private Map<String, Integer> subCompanyHandler(Department department) {
//			企业（总部）id
		int companyid = 1;
//			上级分部id
		int supsubcomid = 0;
		int subCompanyId;
		int subcompanyid1 = 0;
		int supDepId = 0;
		RecordSet rs = new RecordSet();


		Map<String, Integer> map = new HashMap<>();
//			查询部门与手动维护的分部信息，并进行同步
		String subCompanySql = MessageFormat.format("select * from uf_subcompany where yjbmid = ''{0}''", String.valueOf(department.getDEPARTMENTID()));
		if (rs.executeQuery(subCompanySql) && rs.next()) {
			String subCompanyName = Util.null2String(rs.getString("fbmmc"));
			String subCompanyDisc = Util.null2String(rs.getString("fbmms"));
			subCompanyId = Util.getIntValue(Util.null2String(rs.getString("fbmid")));
//				查询分部表，是否存在该分部
//				rs.executeQuery("select * from hrmsubcompany where id = ?", subCompanyId);
			try {
				rs.executeQuery("select * from hrmsubcompany where outkey = ?", Util.getIntValue(String.valueOf(subCompanyId)));
			} catch (Exception e) {
				writeErrorLog("select hrmsubcompany error: select * from hrmsubcompany where outkey = " + Util.getIntValue(String.valueOf(subCompanyId)));
				writeErrorLog("error info:" + e.getMessage() + "\n");
			}
			if (rs.next()) {
//					已经存在分部信息,保存部门对应分部id
				subcompanyid1 = Util.getIntValue(Util.null2String(rs.getString("id")));
//					设置上级部门id为0
				supDepId = 0;
//					更新分部信息
				String updateSql = "update hrmsubcompany set " +
						"subcompanyname = '" + subCompanyName +
						"', subcompanydesc = '" + subCompanyDisc +
						"', modified = '" + this.getTime() +
						"' where id = " + subcompanyid1;
				rs.executeUpdate(updateSql);

			} else {
//					不存在分部信息
				String insertSql = "insert into hrmsubcompany " +
						"(outkey,companyid,subcompanyname,subcompanydesc,created,supsubcomid) values ('"
						+ subCompanyId + "', '" + companyid + "', '" + subCompanyName + "','" + subCompanyDisc + "','"
						+ this.getTime() + "','" + supsubcomid + "')";
				rs.executeUpdate(insertSql);
//					查询刚插入的分部id
				try {
					rs.executeQuery("select * from hrmsubcompany where outkey = ?", subCompanyId);
				} catch (Exception e) {
					writeErrorLog("select hrmsubcompany error: select * from hrmsubcompany where outkey = " + subCompanyId);
					writeErrorLog("error info:" + e.getMessage() + "\n");
				}
				rs.next();
				subcompanyid1 = Util.getIntValue(Util.null2String(rs.getString("id")));
			}
		}
		map.put("subcompanyid1", subcompanyid1);
		map.put("supDepId", supDepId);
		return map;
	}

	private Map<String, StringBuilder> builderSql(Map<String, String> filedAndValue, int type) {
		StringBuilder sql = new StringBuilder();
		Map<String, StringBuilder> map = new HashMap<>();
		if (type == 1) {
//			拼接更新SQL
			for (Map.Entry<String, String> entry : filedAndValue.entrySet()) {
				sql.append(entry.getKey()).append(" = '");
				sql.append(entry.getValue()).append("',");
			}
			map.put("update", sql);
			return map;
		}
		if (type == 2) {
//			拼接插入SQL
			StringBuilder sysInsertSqlField = new StringBuilder();
			StringBuilder sysInsertSqlValue = new StringBuilder();
			for (Map.Entry<String, String> entry : filedAndValue.entrySet()) {
				sysInsertSqlField.append(entry.getKey());
				sysInsertSqlField.append(",");
				sysInsertSqlValue.append("'");
				sysInsertSqlValue.append(entry.getValue());
				sysInsertSqlValue.append("',");
			}
			map.put("insertField", sysInsertSqlField);
			map.put("insertValue", sysInsertSqlValue);
			return map;
		}
		return map;
	}

	private int insertJobGroup(Position position) {
		RecordSet rs = new RecordSet();
		String insertJobGroups = MessageFormat.format("insert into hrmjobgroups (" +
						"jobgroupname,jobgroupremark,created,outkey) values (" +
						"''{0}'',''{1}'',''{2}'',''{3}'')",
				position.getJOBFUNCTION(), position.getJOBFUNCTION(), this.getTime(), String.valueOf(position.getJOBCODEID()));

		try {
			rs.executeUpdate(insertJobGroups);
		} catch (Exception e) {
			writeErrorLog("insert hrmjobgroups error: " + insertJobGroups);
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}
		try {
			rs.executeQuery("select * from hrmjobgroups where outkey = ?", Util.getIntValue(String.valueOf(position.getJOBCODEID())));
		} catch (Exception e) {
			writeErrorLog("select hrmjobgroups error: select * from hrmjobgroups where outkey = " + Util.getIntValue(String.valueOf(position.getJOBCODEID())));
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}
		rs.next();
		return Util.getIntValue(Util.null2String(rs.getString("id")));
	}

	private int insertJobActive(Position position, int groupId) {
		RecordSet rs = new RecordSet();
		String insertJobActive = MessageFormat.format("insert into hrmjobactivities (" +
						"jobactivitymark,jobactivityname,jobgroupid,created,outkey) values (" +
						"''{0}'',''{1}'',''{2}'',''{3}'',''{4}'')",
				position.getJOBFUNCTION(), position.getJOBFUNCTION(), String.valueOf(groupId), this.getTime(), String.valueOf(position.getJOBCODEID()));

		try {
			rs.executeUpdate(insertJobActive);
		} catch (Exception e) {
			writeErrorLog("insert hrmjobactivities error: " + insertJobActive);
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}

		try {
			rs.executeQuery("select * from hrmjobactivities where outkey = ?", Util.getIntValue(String.valueOf(position.getJOBCODEID())));
		} catch (Exception e) {
			writeErrorLog("select hrmjobactivities error: select * from hrmjobactivities where outkey = " + Util.getIntValue(String.valueOf(position.getJOBCODEID())));
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}
		rs.next();
		return Util.getIntValue(Util.null2String(rs.getString("id")));
	}

	private void insertJobTitle(Position position, RecordSet rs, int activityId, int cmsSupJobId) {
		String insertJobTitle = MessageFormat.format("insert into hrmjobtitles (" +
						"jobtitlemark,jobtitlename,jobactivityid,jobtitlecode,created,outkey,cmssupjobid) values (" +
						"''{0}'',''{1}'',''{2}'',''{3}'',''{4}'',''{5}'',''{6}'')",
				position.getJOBFUNCTION(), position.getJOBFUNCTION(), String.valueOf(activityId), position.getJOBCODE(),
				this.getTime(), String.valueOf(position.getJOBCODEID()), String.valueOf(cmsSupJobId));
		try {
			rs.executeUpdate(insertJobTitle);
		} catch (Exception e) {
			writeErrorLog("insert hrmjobtitles error: " + insertJobTitle);
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}

	}

	/**
	 * 查询配置表字段信息
	 *
	 * @return
	 */
	public Map<String, Map<String, String>> queryConfigTableInfo(Object obj, GetModelValue getModelValue, String conditions) {
		RecordSet rs = new RecordSet();
		Map<String, Map<String, String>> configResults = new HashMap<>();
		try {
			rs.executeQuery("select d.id,m.api_field_type,d.api_field,d.change_rules,d.custom_rules_value,d.ao_field,oa_field_type from uf_cms_async as m " +
					"inner join uf_cms_async_dt1 as d on d.mainid = m.id where " + conditions);
		} catch (Exception e) {
			writeErrorLog("select configTable error: " + "select d.id,m.api_field_type,d.api_field,d.change_rules,d.custom_rules_value,d.ao_field,oa_field_type from uf_cms_async as m " +
					"inner join uf_cms_async_dt1 as d on d.mainid = m.id where " + conditions);
			writeErrorLog("error info:" + e.getMessage() + "\n");
		}
		Map<String, String> sysDep = new HashMap<>();
		Map<String, String> defDep = new HashMap<>();
//		 判断转换规则，如果是自定义SQL，则需要先执行SQL，如果是固定值，则需要进行直接赋值，如果是不转换，则按照默认走
		while (rs.next()) {
			String apiFieldType = Util.null2String(rs.getString("api_field_type"));
			String oaFieldType = Util.null2String(rs.getString("oa_field_type"));
			String apiField = Util.null2String(rs.getString("api_field"));
			int changeRules = Util.getIntValue(Util.null2String(rs.getString("change_rules")));
			if (changeRules == 0) {
//				转换规则为不转换,获取api字段对应的值
				try {
					apiField = (String) getModelValue.getValueForString(obj, apiField);
				} catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException e) {
					this.writeErrorLog(className, "changeRules error,args is: apiField[" + apiField + "],obj[" + obj + "]");
					e.printStackTrace();
				}
			} else if (changeRules == 2) {
//				转换规则为自定义SQL
				String customRulesValue = Util.null2String(rs.getString("custom_rules_value"));
				if (customRulesValue.contains("delete") || customRulesValue.contains("update") ||
						customRulesValue.contains("exec") || customRulesValue.contains("drop") ||
						customRulesValue.contains("truncate")) {
//					sql 不包含delete，update，exec，drop，truncate等危险关键字
				} else if (customRulesValue.contains("select")) {
//					包含查询关键词
					try {
						rs.executeQuery(customRulesValue);
					} catch (Exception e) {
						writeErrorLog("execute custom sql error: " + customRulesValue);
						writeErrorLog("error info:" + e.getMessage() + "\n");
					}
					rs.next();
					apiField = Util.null2String(rs.getString(1));
				}
			} else {
				apiField = Util.null2String(rs.getString("custom_rules_value"));
			}
//			如果属于系统字段
			if ("0".equals(oaFieldType)) {
				sysDep.put(Util.null2String(rs.getString("ao_field").replace(apiFieldType + oaFieldType, "")), apiField);
			} else {
//				属于自定义字段
				defDep.put(Util.null2String(rs.getString("ao_field").replace(apiFieldType + oaFieldType, "")), apiField);
			}
		}
		configResults.put("sys", sysDep);
		configResults.put("def", defDep);
		return configResults;
	}

	public String getTime() {
		Date date = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
		return formatter.format(date);
	}
}
